﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;

/// <summary>
/// Summary description for mapDAO
/// </summary>
public class mapDAO : DataProvider
{
    //Lay toan bộ thông tin bang MAPS
    public List<mapDTO> getListMap()
    {
        List<mapDTO> mapList = new List<mapDTO>();
        OleDbConnection connect = null;
        try
        {
            connect = Connect();
            string strCommand = "SELECT * FROM MAPS ";
            OleDbCommand command = new OleDbCommand(strCommand, connect);
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                mapDTO map = new mapDTO();
                map.ID = reader.GetInt32(0);
                map.User = reader.GetString(1);
                map.Name = reader.GetString(2);
                map.Latitude = reader.GetDouble(3);
                map.Longitude = reader.GetDouble(4);
                map.Address = reader.GetString(5);
                map.Type = reader.GetInt32(6);
                map.Status = reader.GetInt32(7);
                mapList.Add(map);
            }
        }
        catch (Exception ex)
        {
            mapList = null;
        }
        finally
        {
            if (connect != null && connect.State == System.Data.ConnectionState.Open)
                connect.Close();
        }
        return mapList;
    }
    //Lay thong tin theo tên đang nhap
    public List<mapDTO> getListMapByUser(string _username)
    {
        List<mapDTO> mapList = new List<mapDTO>();
        OleDbConnection connect = null;
        try
        {
            connect = Connect();
            string strCommand = "SELECT * FROM MAPS WHERE user = @user";
            OleDbCommand command = new OleDbCommand(strCommand, connect);

            OleDbParameter param;
            param = new OleDbParameter("@user", OleDbType.VarChar);
            param.Value = _username;
            command.Parameters.Add(param);
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                mapDTO map = new mapDTO();
                map.ID = reader.GetInt32(0);
                map.User = reader.GetString(1);
                map.Name = reader.GetString(2);
                map.Latitude = reader.GetDouble(3);
                map.Longitude = reader.GetDouble(4);
                map.Address = reader.GetString(5);
                map.Type = reader.GetInt32(6);
                map.Status = reader.GetInt32(7);
                mapList.Add(map);
            }
        }
        catch (Exception ex)
        {
            mapList = null;
        }
        finally
        {
            if (connect != null && connect.State == System.Data.ConnectionState.Open)
                connect.Close();
        }
        return mapList;
    }
    //Lay thong tin theo kieu(type)
    public List<mapDTO> getListMapByType(int _type)
    {
        List<mapDTO> mapList = new List<mapDTO>();
        OleDbConnection connect = null;
        try
        {
            connect = Connect();
            string strCommand = "SELECT * FROM MAPS WHERE type = @type";
            OleDbCommand command = new OleDbCommand(strCommand, connect);

            OleDbParameter param;
            param = new OleDbParameter("@type", OleDbType.Integer);
            param.Value = _type;
            command.Parameters.Add(param);

            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                mapDTO map = new mapDTO();
                map.ID = reader.GetInt32(0);
                map.User = reader.GetString(1);
                map.Name = reader.GetString(2);
                map.Latitude = reader.GetDouble(3);
                map.Longitude = reader.GetDouble(4);
                map.Address = reader.GetString(5);
                map.Type = reader.GetInt32(6);
                map.Status = reader.GetInt32(7);
                mapList.Add(map);
            }
        }
        catch (Exception ex)
        {
            mapList = null;
        }
        finally
        {
            if (connect != null && connect.State == System.Data.ConnectionState.Open)
                connect.Close();
        }
        return mapList;
    }
    //Lay thong tin theo kinh độ vi độ va ten nguoi dang nhap
    public  mapDTO getMapByLatitudeAndLongitude(string _username, double _latitude, double _longitude)
    {
        mapDTO map = new mapDTO();
        OleDbConnection connect = null;
        try
        {
            connect = Connect();
            string strCommand = "SELECT * FROM MAPS WHERE user=@user AND latitude=@latitude AND longitude=@longitude ";
            OleDbCommand command = new OleDbCommand(strCommand, connect);

            OleDbParameter param;
            param = new OleDbParameter("@user", OleDbType.VarChar);
            param.Value = _username;
            command.Parameters.Add(param);
            param = new OleDbParameter("@latitude", OleDbType.Double);
            param.Value = _latitude;
            command.Parameters.Add(param);
            param = new OleDbParameter("@longitude", OleDbType.Double);
            param.Value = _longitude;
            command.Parameters.Add(param);
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                map.ID = reader.GetInt32(0);
                map.User = reader.GetString(1);
                map.Name = reader.GetString(2);
                map.Latitude = reader.GetDouble(3);
                map.Longitude = reader.GetDouble(4);
                map.Address = reader.GetString(5);
                map.Type = reader.GetInt32(6);
                map.Status = reader.GetInt32(7);
            }
        }
        catch (Exception ex)
        {
            map = null;
        }
        finally
        {
            if (connect != null && connect.State == System.Data.ConnectionState.Open)
                connect.Close();
        }
        return map;
    }
    //Them vao 1 dong
    public bool insertMap(mapDTO map)
    {
        bool result = true;
        OleDbConnection connect = null;
        try
        {
            connect = Connect();
            string strCommand = "INSERT INTO MAPS(user, name, latitude, longitude, address, type) VALUES(@user, @name, @latitude, @longitude, @address, @type)";
            OleDbCommand command = new OleDbCommand(strCommand, connect);
            OleDbDataReader reader = command.ExecuteReader();

            OleDbParameter param;
            param = new OleDbParameter("@user", OleDbType.VarChar);
            param.Value = map.User;
            command.Parameters.Add(param);
            param = new OleDbParameter("@name", OleDbType.VarChar);
            param.Value = map.Name;
            command.Parameters.Add(param);
            param = new OleDbParameter("@latitude", OleDbType.Double);
            param.Value = map.Latitude;
            command.Parameters.Add(param);
            param = new OleDbParameter("@longitude", OleDbType.Double);
            param.Value = map.Longitude;
            command.Parameters.Add(param);
            param = new OleDbParameter("@address", OleDbType.VarChar);
            param.Value = map.Address;
            command.Parameters.Add(param);
            param = new OleDbParameter("@type", OleDbType.Integer);
            param.Value = map.Type;
            command.Parameters.Add(param);

            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            result = false;
        }
        finally
        {
            if (connect != null && connect.State == System.Data.ConnectionState.Open)
                connect.Close();
        }
        return result;
    }
    //Cap nhat trang thai
    public bool updateStatus(string _username, double _latitude, double _longitude, int _status)
    {
        bool result = true;
        OleDbConnection connect = null;
        try
        {
            connect = Connect();
            string strCommand = "UPDATE MAPS SET status=@status WHERE user=@user AND latitude=@latitude AND longitude=@longitude";
            OleDbCommand command = new OleDbCommand(strCommand, connect);
            OleDbDataReader reader = command.ExecuteReader();

            OleDbParameter param;
            param = new OleDbParameter("@status", OleDbType.Integer);
            param.Value = _status;
            command.Parameters.Add(param);
            param = new OleDbParameter("@user", OleDbType.VarChar);
            param.Value = _username;
            command.Parameters.Add(param);
            param = new OleDbParameter("@latitude", OleDbType.Double);
            param.Value = _latitude;
            command.Parameters.Add(param);
            param = new OleDbParameter("@longitude", OleDbType.Double);
            param.Value = _longitude;
            command.Parameters.Add(param);

            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            result = false;
        }
        finally
        {
            if (connect != null && connect.State == System.Data.ConnectionState.Open)
                connect.Close();
        }
        return result;
    }
}